<?php
class Mgis extends CI_Model {

	function Mgis(){
		parent::__construct();
                $this->idnya = "";
                $this->result = "";
                $this->result_array = array();
                $this->user_group_arr = array();
                $this->pathTer = "";
	}
	
	public function getMapTracking($user_id, $tanggal ) {        

            //$query = $this->db->where(array('user_id' =>$user_id,'track_date'=>$tanggal))->get('t_trx_track');
           /**
 *  $sql = "select track_id, lon, lat, created_on, journey_id from (select DISTINCT ON (lon,lat) lon, lat, track_id, created_on, journey_id ".
 *                    "from t_trx_track where user_id = '$user_id' and track_date = '$tanggal' and lon <> '0.0' and lat <> '0.0' ".
 *                     "order by lon,lat,track_id asc) as y order by track_id";
 */
 
            //SQL Patched By RAA
            $sql="select y.track_id, y.lon, y.lat, y.created_on, y.journey_id,y.accuracy,j.channel_id,coalesce(coalesce(o.outlet_name,ag.agent_name),NULL) as partner_name, j.type, concat(coalesce(o.address,'null'),',',coalesce(o.city,'null'),'<br>Outlet LonLat:',coalesce(o.lon,'null'),',',coalesce(o.lat,'null')) as address from (select DISTINCT ON (lon,lat) lon, lat, track_id, created_on, journey_id,accuracy
                  from t_trx_track where user_id = '$user_id' and track_date = '$tanggal' and lon <> '0.0' and lat <> '0.0' 
                    order by lon,lat,journey_id asc) as y 
left join t_trx_journey j on j.journey_id=y.journey_id
left join t_mtr_outlet o on o.outlet_id =j.channel_id
left join t_mtr_agent ag on ag.agent_id=j.channel_id
order by track_id";
            $query = $this->db->query($sql);
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
        public function getMapOutlet($outlet_id){
            $sql = "select outlet_id, outlet_name, concat_ws(', ',address, city), lon, lat
            from t_mtr_outlet ott
            join t_mtr_territory tt
            on ott.territory_id = tt.territory_id
            and tt.parent_id = 58
            and ott.outlet_id = '$outlet_id'
            and ott.lon is not NULL
	        and ott.lon != '0'
	        and ott.lon != '0.0'
            and ott.lon != ''
	        and ott.lat is not null
            and ott.lat != '0'
	        and ott.lat != '0.0'
	        and ott.lat != '';";
            $query = $this->db->query($sql);
            if ($query->num_row() > 0){
                return $query->result();
            } else {
                return array();
            }
            
        }
        
        public function getFirstTrack($lon,$lat,$user_id,$tanggal) {
            $sql = "select created_on as mydate from t_trx_track where lon = '$lon' and lat = '$lat' and user_id = '$user_id' and track_date = '$tanggal' order by mydate asc limit 1";
            $query = $this->db->query($sql);
            $row = $query->row();
                    
            return $row->mydate;
            
        }
        public function getLastTrack($lon,$lat,$user_id,$tanggal) {
            $sql = "select created_on as mydate from t_trx_track where lon = '$lon' and lat = '$lat' and user_id = '$user_id' and track_date = '$tanggal' order by mydate desc limit 1";
            $query = $this->db->query($sql);
            $row = $query->row();
            
            return $row->mydate;
            
        }
        
        public function getMinute($his) {
            $str = explode(':',$his);
            $min = (int)$str[0] * 60;
            return $min + $str[1];
            
        }
        public function getDurationTracking($lon,$lat,$user_id,$tanggal) {
            $firstTrack = $this->getFirstTrack($lon,$lat,$user_id,$tanggal);
            $lastTrack = $this->getLastTrack($lon,$lat,$user_id,$tanggal);
            
            $datetime1 = new DateTime($firstTrack);
            $datetime2 = new DateTime($lastTrack);
            $interval = $datetime1->diff($datetime2);
            //return $interval->format('%H:%i:%s');
            $int = $interval->format('%H:%i:%s');
            return $this->getMinute($int);
            
            //return $firstTrack ." ".$lastTrack;
            
        }
        
        public function getUserName($user) {
            $sql = "select user_name from t_mtr_user where user_id = '$user'";           
            $query = $this->db->query($sql);
            $row = $query->row();
            return $row->user_name;
        }
        
        public function getgroupname($user) {
            $sql = "select t_mtr_user_group.user_group_name from t_mtr_user join t_mtr_user_group on t_mtr_user.user_group_id = t_mtr_user_group.user_group_id where user_id = '$user'";           
            $query = $this->db->query($sql);
            $row = $query->row();
            return $row->user_group_name;
        }
        
//        public function getGroupID() {
//            $user = $this->session->userdata( 'username' );
//            $query = $this->db->query("select user_group_id from t_mtr_user a where a.user_id = '$user'");
//            $row = $query->row();
//            
//            
//            return $row->user_group_id;
//        }
//        
//        public function cekChildGroup($parent_id) {
//            $query = $this->db->query("select user_group_id from t_mtr_user_group where parent_id = '$parent_id'");
//            return $query;                                  
//            
//        }
//        public function getGroupsID($parent_id = 0) {
//            $this->user_group_arr[] = $parent_id;
//            $query = $this->cekChildGroup($parent_id);
//            if( $query->num_rows() > 0 ) {
//                foreach($query->result() as $r){                                
//                        $this->user_group_arr[] = $r->user_group_id;
//                        
//                        //cek lagi apa punya anak
//                        $cek = $this->cekChildGroup($r->user_group_id);  
//                        if($cek->num_rows() > 0) {
//                            $this->user_group_arr[] = $r->user_group_id;
//                            $this->getGroupsID($r->user_group_id);
//                        }else {
//                            
//                        }
//                        
//                }
//                
//            }
//            
//            return array_unique($this->user_group_arr);
//        }
//        public function getUser89() {
//            $parent_id = $this->getGroupID();
//            $sql = "select a.* from t_mtr_user a, t_mtr_user_group b where a.user_group_id = b.user_group_id ";
//                       
//            $q = $this->getGroupsID(1);
//                
//            $jum = array_count_values($q);                       
//            if($jum > 0){ 
//                $i = 0;
//                foreach($q as $r) {
//                    if($r == 8 || $r == 9) {
//                        if($i == 0) {                        
//
//                            $sql .= "and (b.user_group_id = '$r' ";
//
//                        }else {
//
//                             $sql .= "or b.user_group_id = '$r' ";
//
//                        }
//                        $i++;
//                    }
//                    
//                }
//            }
//                      
//            $sql .= ") ";
//            $sql .= "order by b.user_group_id asc";
//            $query = $this->db->query($sql);
//            if( $query->num_rows() > 0 ) {
//                return $query;  
//                //return $query->result_array();  
//            } else {
//                return "";
//            }
//        }
        
        
        public function getUser89() {
            $user = $this->session->userdata( 'username' );
            /** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            */
            $cluster = $this->db->query("select * from f_get_cluster('".$user."')");
            $cluster = $cluster->result();
            $cluster = $cluster[0]->f_get_cluster;
            // echo $cluster;die();

            $sql = "select t_mtr_user.user_id, initcap(t_mtr_user.user_name) as user_name, t_mtr_user_group.user_group_name from t_mtr_user join t_mtr_user_group on t_mtr_user.user_group_id = t_mtr_user_group.user_group_id where f_get_cluster(user_id) = '$cluster' ".
                    "and t_mtr_user.user_group_id in (8,9) and t_mtr_user.istatus = 1 ORDER BY user_name ASC";
            $query = $this->db->query($sql);
            
            if( $query->num_rows() > 0 ) {
                return $query;  
                //return $query->result_array();  
            } else {
                return "";
            }
        }
        function getUser_json()
        {    
                $k=0;  
                $this->idnya ++;
		$getUser = $this->getUser89();
		$this->result .='[';
		foreach($getUser->result() as $r){
                        $k++;
			$this->result .='{"tid":"'. $r->user_id .'",
                                                "id":"'. $r->user_id .'",
                                                "group_name":"'. $r->user_group_name .'",
                                                "name":"'. $r->user_name .'"}';
			if($k!=$getUser->num_rows) $this->result .=',';
		}
		$this->result .="]";
                return $this->result;
	}
        
        public function getTerritory() {
        

            $query = $this->db->query("select a.* from t_mtr_territory a order by territory_id,parent_id asc");
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
         public function getTerritoryById($territory_id) {
        

            $query = $this->db->query("select * from t_mtr_territory where territory_id = '$territory_id'");
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                //return $query->result();  
                return $query->row_array();  
            } else {
                return array();
            }
        }
        
        public function getOutlet() {
        

            $query = $this->db->query("select * from t_mtr_outlet");
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
        public function getOutletByTerritory($territory_id, $outlet_id, $outlet_name) {
        /*
            $q = $this->getTerritoryMember($territory_id);
            $jum = array_count_values($q);   
            
            //return $q;                  
            if($jum > 0){  
                $sql = "select * from t_mtr_outlet ";
                
                $i = 0;
                foreach($q as $r){  
                    if($i == 0) {
                        $sql .= "where (territory_id = '$r' ";
                    }else {
                        $sql .= "or territory_id = '$r' ";
                    }
                    
                    $i++;
                                        
                }
                $sql .= ") and outlet_id like '%$outlet_id%' and outlet_name like '%$outlet_name%' ";
            }else{return 'kosong';}*/
            
            
            $sql = "select * 
            from t_mtr_outlet as tot 
            join t_mtr_territory as tt on tot.territory_id = tt.territory_id 
            where tt.parent_id = '$territory_id'  
            and tot.outlet_id like '%$outlet_id%' 
            and tot.outlet_name like '%$outlet_name%' 
            and tt.territory_type_id=5 
            and tot.lon is not NULL
	        and tot.lon != '0'
	        and tot.lon != '0.0'
            and tot.lon != ''
	        and tot.lat is not null
            and tot.lat != '0'
	        and tot.lat != '0.0'
	        and tot.lat != '';";
            
            //return $q;
            $query = $this->db->query($sql);
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
        public function getAgentByTerritory($territory_id, $agent_id, $agent_name) {
        /*
            $q = $this->getTerritoryMember($territory_id);
            $jum = array_count_values($q);
            
                          
            if($jum > 0){  
                $sql = "select * from t_mtr_agent ";
                
                $i = 0;
                foreach($q as $r){  
                    if($i == 0) {
                        $sql .= "where (territory_id = '$r' ";
                    }else {
                        $sql .= "or territory_id = '$r' ";
                    }
                    $i++;
                    
                }
                $sql .= ") and agent_id like '%$agent_id%' and agent_name like '%$agent_name%' ";
            }*/
            
            $sql = "select * from t_mtr_agent as tag join t_mtr_territory as tt on tag.territory_id = tt.territory_id where tt.parent_id = '$territory_id'  and tag.agent_id like '%$agent_id%' and tag.agent_name like '%$agent_name%' and tt.territory_type_id=6 ";
            
            $query = $this->db->query($sql);
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
        public function getTerritoryUserLogin() {
            $user = $this->session->userdata( 'username' );
            $sql = "SELECT territory_id as mvalue, territory_name as mdisplay FROM t_mtr_territory ".
                    "WHERE territory_type_id = 3 and territory_id = f_get_cluster('$user') LIMIT 1";
            $query = $this->db->query($sql);
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {                  
                
                return $query->row();
                //return $query->result_array();  
            } else {
                return "";
            }
        }
        //get status apakah Head Office
        public function getStatusTerritoryUser() {
            $user = $this->session->userdata( 'username' );
            $sql = "SELECT territory_id FROM t_mtr_territory a left join t_mtr_user b on (a.user_id = b.user_id) ".
                    "WHERE a.user_id = '$user' and a.territory_id = '1' and b.user_group_id = '10'";
            $query = $this->db->query($sql);
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {                  
                
                return true;
                //return $query->result_array();  
            } else {
                return false;
            }
        }
        public function getTerritoryMember($id)
        {    
           $this->result_array[] = $id;
                $query = $this->getTerritoryDody($id);
                $k=0;
                $jum = $query->num_rows();                         
                if($query->num_rows()>0){                        
                        foreach($query->result() as $r){                                
                                $this->result_array[] = $r->territory_id;
                                
                                #cek punya children gak ?
                                /*$cek = $this->getTerritoryDody($r->territory_id);$k++;
                                if($cek->num_rows()>0){
                                        $this->result_array[] = $r->territory_id;
                                        
                                        $this->getTerritoryMember($r->territory_id);
                                       
                                }*/
                                
                        }                      
                        
                }
                return  array_unique($this->result_array);
	}
	
        public function getBoundaryTerritory($territory_id) {
        

            $query = $this->db->query("select * from t_mtr_territory_border where territory_id = '$territory_id'");
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        
        function getTerritoryByUserLogin() {
            $user = $this->session->userdata( 'username' );
            $sql = "select * t_mtr_user_territory where user_id = '$user'";
            $query = $this->db->query($sql);
            if( $query->num_rows() > 0 ) {
                return $query->result();  
                //return $query->result_array();  
            } else {
                return array();
            }
        }
        function getTerritoryDody($pid)
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $dblokal->order_by("ordering", "ASC");
                $where = "parent_id = '$pid' and (territory_type_id=1 or territory_type_id=2 or territory_type_id=3) ";
                
                $dblokal->where($where);
                $query = $dblokal->get("t_mtr_territory");
                return $query;
//                $user = $this->session->userdata( 'username' );
//                $sql = "select * from t_mtr_territory a left join t_mtr_user_territory b on(a.territory_id = b.territory_id) where b.user_id = '$user' and a.parent_id = '$pid' and ".
//                        "(a.territory_type_id = 1 or a.territory_type_id = 2 or a.territory_type_id = 3) order by a.ordering ASC";                
//                $query = $this->db->query($sql);

                
                return $query;  
               
	}
    
    
            function getTerritoryDonny($pid)
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $dblokal->order_by("ordering", "ASC");
                $where = "parent_id = '$pid' and territory_type_id in (1,2,3,4,5) ";
                
                $dblokal->where($where);
                $query = $dblokal->get("t_mtr_territory");
                return $query;
//                $user = $this->session->userdata( 'username' );
//                $sql = "select * from t_mtr_territory a left join t_mtr_user_territory b on(a.territory_id = b.territory_id) where b.user_id = '$user' and a.parent_id = '$pid' and ".
//                        "(a.territory_type_id = 1 or a.territory_type_id = 2 or a.territory_type_id = 3) order by a.ordering ASC";                
//                $query = $this->db->query($sql);

                
                return $query;  
               
	}
        
        function getParentIdByUser() {
            $user = $this->session->userdata( 'username' );
            $sql = "select parent_id from t_mtr_territory where user_id = '$user' order by parent_id limit 1";
            $query = $this->db->query($sql);
            $row = $query->row();
            
            return $row->parent_id;
        }
        function getTerritoryTree($id)
        {    
            
                $query = $this->getTerritoryDody($id);
                $k=0;
                $jum = $query->num_rows();                         
                if($query->num_rows()>0){
                        $this->result .='[';
                        foreach($query->result() as $r){                                
                                $this->result .='{"id":'. $r->territory_id .',
                                                "text":"'. $r->territory_name .'",
                                                "state":"open"';
                                #cek punya children gak ?
                                $cek = $this->getTerritoryDody($r->territory_id);$k++;
                                if($cek->num_rows()>0){
                                        $this->result .=',"children":';
                                        $this->getTerritoryTree($r->territory_id);
                                        $this->result .='}';
                                        if($k!=$jum) $this->result .=',';
                                }else{
                                        $this->result .='}';
                                        if($k!=$jum) $this->result .=',';
                                        $this->getTerritoryTree($r->territory_id);
                                }
                                
                        }
                        $this->result .="]";
                        
                }
                return $this->result;
	}
        
        public function putBoundary() {
            $data = array();
            
            if($this->input->post("border") <> "") {                
                
                $this->db->delete( 't_mtr_territory_border', array( 'territory_id' => $this->input->post("territory") ) );
                
                $geo = explode(";",$this->input->post("border"));
                $sum_array = count($geo);
                $x = 0;
                if($sum_array > 0) {
                    foreach($geo as $v => $x) {
                        if($x <> "") {
                            $latlng = explode(",",$x);                     
                            
                            $data[$x] = array(
                                "territory_id" => $this->input->post("territory"),
                                "lat" => $latlng[0],
                                "lon" => $latlng[1]
                            );
                            $x++;
                        }
                    }     
                }
            }

            $this->db->insert_batch( 't_mtr_territory_border', $data );
            return $this->db->insert_id();
        }
        
        public function updateColor() {
            $color_array = array("000000","ff1800","001eff","fff000","06ff00","ff00fc","ff6c00","00fcff","004e72","ab2ff2","aeff00","8f347b","bcbcbc");
            $fill_color = array_rand($color_array,1);
            $border_color = array_rand($color_array,1);
            $data = array(
                'fill_color' => $fill_color,
                'border_color' => $border_color
            );

            $this->db->update( 't_mtr_territory', $data, array( 'territory_id' => $this->input->post( 'territory', true ) ) );
        }
        
        public function updateColor2() {
            $user = $this->session->userdata( 'username' );
            $sql = "SELECT f_get_region_color(f_get_region('$user')) as fill_color";
            
            $query = $this->db->query($sql);
            //$query = $this->db->query("");
            
            if( $query->num_rows() > 0 ) {                  
                
                $row = $query->row();
                $fill_color = $row->fill_color;
                
                //return $query->result_array();  
            } else {
                $fill_color =  "000000";
            }
            $border_color = "000000";
            
            $data = array(
                'fill_color' => $fill_color,
                'border_color' => $border_color
            );

            $this->db->update( 't_mtr_territory', $data, array( 'territory_id' => $this->input->post( 'territory', true ) ) );
        }
            
        
        public function getParentTerritory($id) {
            
            $sql = "select * from t_mtr_territory where territory_id = '$id'";
            $query = $this->db->query($sql);
            $i = 0;
            
            if($query->num_rows()>0){
                $this->pathTer .= "";
                $row = $query->row();

                $this->pathTer .= $row->territory_name ." > ";
                
                if($id <> $row->parent_id) {
                $this->mgis->getParentTerritory($row->parent_id);
                }                        
                   
                
            }else {
               $this->pathTer .= "";
            }
            
            return $this->pathTer;
            
        }
        function updateoutletlonlat($channel,$lon,$lat){
                $user = $this->session->userdata( 'username' );
              $sql = "select f_set_partner_longlat('$channel','$lon','$lat','$user') ";
              $query = $this->db->query($sql);
              if($query->num_rows()>0){
                  return $query->row()->f_set_partner_longlat;
              }else{
                return 0;
                }

              
              return  $this->db->update( 't_mtr_outlet', array("lon"=>$lon,"lat"=>$lat), array( 'outlet_id' => $channel ) );
        }
}